package com.example.demo.excel.util;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
    /**
     * @param @param fileName  Excel文件名称
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @param @param text		模板说明
     * @param @param listred	需加红的列
     * @param @param textRows	需转换为text格式的列
     * @param @param contentList	需填写的行的默认部分数据
     * @return void
     * @throws IOException
     * @throws UnsupportedEncodingException
     * @Description: 保护数据，带有锁
     */
    public static void createClassTemplateWithData(HttpServletResponse response, String fileName, String[] handers,
                                                   List<String[]> downData, List<Integer> downRows, String text, List<Integer> listred, List<Integer> textRows, List<Map<String, Object>> contentList) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Sheet0");
        //加锁样式
        XSSFCellStyle lockstyle = wb.createCellStyle();
        lockstyle.setLocked(true);//设置锁定---所有单元格会默认上锁
        lockstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());//设置上锁的单元格背景色
        //不加锁样式
        XSSFCellStyle unlockStyle = wb.createCellStyle();
        unlockStyle.setLocked(false);//设置未锁定

        if (textRows != null && textRows.size() > 0) {
            // 设置单元格格式为文本格式
            XSSFCellStyle textStyle = wb.createCellStyle();
            XSSFDataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            //设置单元格格式为"文本" 只对加红列设置
            for (Integer column : listred) {
                sheet.setDefaultColumnStyle(column, textStyle);
            }
        }
        //第一行
        XSSFRow head0 = sheet.createRow(0);
        head0.setHeight((short) 1800);
        XSSFFont font = (XSSFFont) wb.createFont();
        font.setColor(HSSFColorPredefined.RED.getIndex());
        font.setBold(true);
        XSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        for (int i = 0; i < handers.length; i++) {
            sheet.setDefaultColumnStyle(i, unlockStyle);
            XSSFCell cell = head0.createCell(i);
            //默认不加锁
            if (i == 0) {
                cell.setCellStyle(style);
                cell.setCellValue(new XSSFRichTextString(text));
            }
        }
        Integer num = handers.length - 1;
        if (num < 3) {
            num = 4;
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
        XSSFCellStyle style2 = wb.createCellStyle();
        XSSFFont font2 = (XSSFFont) wb.createFont();
        font2.setBold(true);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
        style2.setFont(font2);
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.CENTER);
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());

        //第二行
        XSSFRow head = sheet.createRow(1);
        for (int i = 0; i < handers.length; i++) {
            sheet.setColumnWidth(i, 5000);
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(handers[i]);
            if (listred.contains(i)) {
                cell.setCellStyle(style2);
            } else {
                cell.setCellStyle(style3);
            }
        }
        for (int i = 0; i < downRows.size(); i++) {
            Integer firstcol = downRows.get(i);
            String[] textList = downData.get(i);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);
            CellRangeAddressList addressList = null;
            XSSFDataValidation validation = null;

            addressList = new CellRangeAddressList(2, 20000, firstcol, firstcol);
            validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);

            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
        //第2行以下的内容
        for (int i = 0; i < contentList.size(); i++) {
            XSSFRow headn = sheet.createRow((2 + i));

            XSSFCell cell = headn.createCell(0);
            cell.setCellValue(String.valueOf(contentList.get(i).get("id")));

            cell = headn.createCell(1);
            cell.setCellValue(String.valueOf(contentList.get(i).get("zsxm")));

            cell = headn.createCell(2);
            cell.setCellValue(String.valueOf(contentList.get(i).get("bjmc")));

            cell = headn.createCell(3);
            cell.setCellValue(String.valueOf(contentList.get(i).get("xb")));

            cell = headn.createCell(4);
            cell.setCellValue(String.valueOf(contentList.get(i).get("zjlx")));

            cell = headn.createCell(5);
            cell.setCellValue(String.valueOf(contentList.get(i).get("zjhm")));
        }

        sheet.protectSheet(new String("333"));//333是密码
        sheet.enableLocking();
        CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
        sheetProtection.setSelectLockedCells(false);
        sheetProtection.setSelectUnlockedCells(false);
        sheetProtection.setFormatCells(true);
        sheetProtection.setFormatColumns(true);
        sheetProtection.setFormatRows(true);
        sheetProtection.setInsertColumns(false);//插入行
        sheetProtection.setInsertRows(true);
        sheetProtection.setInsertHyperlinks(true);
        sheetProtection.setDeleteColumns(true);
        sheetProtection.setDeleteRows(false);//删除行
        sheetProtection.setSort(false);
        sheetProtection.setAutoFilter(false);
        sheetProtection.setPivotTables(true);
        sheetProtection.setObjects(true);
        sheetProtection.setScenarios(true);

        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    /**
     * @param @param fileName Excel文件名称
     * @param @param handers Excel列标题(数组)
     * @param @param downData 下拉框数据(数组)
     * @param @param downRows 下拉列的序号(数组,序号从0开始)
     * @param @param text模板说明
     * @param @param listred 需加红的列
     * @param @param textRows 需转换为text格式的列
     * @param @param List<Map<String, String>> mapList 默认塞入数据
     * @param @param listhidden 需加隐藏的列
     * @return void @throws
     * @throws IOException
     * @throws UnsupportedEncodingException
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static void createExcelTemplate(HttpServletResponse response, String fileName, String[] handers,
                                           List<String[]> downData, List<Integer> downRows, String text, List<Integer> listred, List<Integer> textRows,
                                           List<Map<String, String>> mapList, List<Integer> listhidden) throws IOException {

        // 创建工作簿---07版本，没有限制
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = wb.createSheet("Sheet0");

        for (int i = 0; i < listhidden.size(); i++) {
            sheet.setColumnHidden(listhidden.get(i), true);
        }

        if (textRows != null && textRows.size() > 0) {
            // 设置单元格格式为文本格式
            XSSFCellStyle textStyle = wb.createCellStyle();
            XSSFDataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            // 设置单元格格式为"文本" 只对加红列设置
            for (Integer column : listred) {
                sheet.setDefaultColumnStyle(column, textStyle);
            }
        }

        // 创建一行
        XSSFRow head0 = sheet.createRow(0);
        head0.setHeight((short) 1350);

        XSSFFont font = (XSSFFont) wb.createFont();
        font.setColor(HSSFColorPredefined.RED.getIndex());
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);// 设置字体大小
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.JUSTIFY); // 水平居中
//		style.setVerticalAlignment(style.VERTICAL_CENTER);
        style.setFont(font);
        style.setWrapText(true);
        for (int i = 0; i < handers.length; i++) {
            // 创建单元格
            XSSFCell cell = head0.createCell(i);
            if (i == 0) {
                cell.setCellStyle(style);
                cell.setCellValue(new XSSFRichTextString(text));// 加入值
            }
        }
        Integer num = handers.length - 1;
        if (num < 1) {
            num = 2;
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
        XSSFCellStyle style2 = wb.createCellStyle();
        XSSFFont font2 = (XSSFFont) wb.createFont();
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 16);// 设置字体大小

        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());// 设置excel数据字体颜色，红色
        style2.setAlignment(HorizontalAlignment.CENTER); // 居中
        style2.setFont(font2);
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.CENTER);
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());

        // 第二行
        XSSFRow head = sheet.createRow(1);
        head.setHeight((short) 500);
        for (int i = 0; i < handers.length; i++) {
            sheet.setColumnWidth(i, 8000);// 宽4000
            // 创建单元格
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(handers[i]);// 值
            if (listred.contains(i)) {
                cell.setCellStyle(style2);
            } else {
                cell.setCellStyle(style3);
            }
        }

        // 第三行---初始数据
        XSSFRow head2 = sheet.createRow(2);
        head2.setHeight((short) 400);
        for (int i = 0; i < handers.length; i++) {
            // 取map的第一列
            Map<String, String> m = mapList.get(0);
            // 得到第一个value
            String k = m.get(handers[i]);
            XSSFCell cell2 = head2.createCell(i); // 一格
            cell2.setCellValue(k);// 值

        }

        for (int i = 0; i < downRows.size(); i++) {
            Integer firstcol = downRows.get(i);
            String[] textList = downData.get(i);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(textList);
            CellRangeAddressList addressList = null;
            XSSFDataValidation validation = null;

            addressList = new CellRangeAddressList(2, 20000, firstcol, firstcol);
            validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);

            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }

        String fileNameURL = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameURL + ".xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();

        wb.write(response.getOutputStream());// 工作簿输出
        wb.close();
        outputStream.flush();
        outputStream.close();// 关闭流
    }

    /**
     * @param @param fileName Excel文件名称
     * @param @param handers Excel列标题(数组)
     * @param @param downData 下拉框数据(数组)
     * @param @param downRows 下拉列的序号(数组,序号从0开始)
     * @param @param text模板说明
     * @param @param listred 需加红的列
     * @param @param textRows 需转换为text格式的列
     * @return void @throws
     * @throws IOException
     * @throws UnsupportedEncodingException
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static void createExcelTemplate(HttpServletResponse response, String fileName, String[] handers,
                                           List<String[]> downData, List<Integer> downRows, String text, List<Integer> listred, List<Integer> textRows)
            throws IOException {

        // 创建工作簿---07版本，没有限制
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = wb.createSheet("Sheet0");

        if (textRows != null && textRows.size() > 0) {
            // 设置单元格格式为文本格式
            XSSFCellStyle textStyle = wb.createCellStyle();
            XSSFDataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            // 设置单元格格式为"文本" 只对加红列设置
            for (Integer column : listred) {
                sheet.setDefaultColumnStyle(column, textStyle);
            }
        }

        // 创建一行
        XSSFRow head0 = sheet.createRow(0);
        head0.setHeight((short) 1500);
        XSSFFont font = (XSSFFont) wb.createFont();
        font.setColor(HSSFColorPredefined.RED.getIndex());
        font.setBold(true);
        XSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        for (int i = 0; i < handers.length; i++) {
            // 创建单元格
            XSSFCell cell = head0.createCell(i);
            if (i == 0) {
                cell.setCellStyle(style);
                cell.setCellValue(new XSSFRichTextString(text));// 加入值
            }
        }
        Integer num = handers.length - 1;
        if (num < 3) {
            num = 4;
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
        XSSFCellStyle style2 = wb.createCellStyle();
        XSSFFont font2 = (XSSFFont) wb.createFont();
        font2.setBold(true);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
        style2.setFont(font2);
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.CENTER);
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());

        // 第二行
        XSSFRow head = sheet.createRow(1);
        for (int i = 0; i < handers.length; i++) {
            sheet.setColumnWidth(i, 5000);
            // 创建单元格
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(handers[i]);// 值
            if (listred.contains(i)) {
                cell.setCellStyle(style2);
            } else {
                cell.setCellStyle(style3);
            }
        }
        for (int i = 0; i < downRows.size(); i++) {
            Integer firstcol = downRows.get(i);
            String[] textList = downData.get(i);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(textList);
            CellRangeAddressList addressList = null;
            XSSFDataValidation validation = null;

            addressList = new CellRangeAddressList(2, 20000, firstcol, firstcol);
            validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);

            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }

        String fileNameURL = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameURL + ".xlsx");

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();

        wb.write(response.getOutputStream());// 工作簿输出
        wb.close();
        outputStream.flush();
        outputStream.close();// 关闭流
    }

    /**
     * @param @param fileName Excel文件名称
     * @param @param handers Excel列标题(数组)
     * @param @param listred 需加红的列
     * @param @param downData 下拉框数据(数组)
     * @param @param downRows 下拉列的序号(数组,序号从0开始)
     * @return void @throws
     * @throws IOException
     * @throws UnsupportedEncodingException
     * @Title:createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static void createExcelTemplate(HttpServletResponse response, String fileName, List<String> handers,
                                           List<Integer> listred, List<String[]> downData, List<Integer> downRows) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Sheet0");
        XSSFCellStyle style2 = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();
        style2.setDataFormat(format.getFormat("@"));
        XSSFFont font2 = (XSSFFont) wb.createFont();
        //font2.setColor(HSSFColor.RED.index);
        font2.setColor(HSSFColorPredefined.RED.getIndex());
        style2.setFont(font2);
        XSSFRow head = sheet.createRow(0);
        for (int i = 0; i < handers.size(); i++) {
            sheet.setColumnWidth(i, 5000);
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(handers.get(i));
            if (listred.contains(i)) {
                cell.setCellStyle(style2);
            }
        }
        for (int i = 0; i < downRows.size(); i++) {
            Integer firstcol = downRows.get(i);
            String[] textList = downData.get(i);
            if (textList.length > 10) {
                wb = setLongHSSFValidation(wb, textList, sheet, firstcol, i);
            } else {
                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                        .createExplicitListConstraint(textList);
                CellRangeAddressList addressList = null;
                XSSFDataValidation validation = null;
                addressList = new CellRangeAddressList(1, 20000, firstcol, firstcol);
                validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
                sheet.addValidationData(validation);
            }

        }

        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition",
                "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    public static XSSFWorkbook setLongHSSFValidation(XSSFWorkbook workbook, String[] deptList, XSSFSheet sheet,
                                                     int cellNum, int sheetIndex) {
        String hiddenName = "hidden" + cellNum;
        // 1.创建隐藏的sheet页。 起个名字吧！叫"hidden"！
        XSSFSheet hidden = workbook.createSheet(hiddenName);
        // 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
        for (int i = 0, length = deptList.length; i < length; i++) {
            hidden.createRow(20000 + i).createCell(cellNum).setCellValue(deptList[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenName);
        // 3 A1:A代表隐藏域创建第N列createCell(N)时。以A1列开始A行数据获取下拉数组
        category1Name.setRefersToFormula(hiddenName + "!A1:A" + (deptList.length + 20000));
        //
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenName);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 20000, cellNum, cellNum);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof XSSFDataValidation) {
            // 数据校验
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        // 作用在目标sheet上
        sheet.addValidationData(dataValidation);
        // 设置hiddenSheet隐藏
        workbook.setSheetHidden(sheetIndex, true);
        return workbook;
    }

    public static void createErrorExcel(HttpServletResponse response, String fileName, String[] handers,
                                        List<String[]> downData, List<Integer> downRows, ArrayList<String> titleKeyList,
                                        Map<String, String> titleMap, List<Map<String, Object>> src_list) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = null;
        Cell nCell = null; // 列对象
        Row nRow = null;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        for (int k = 0; k < src_list.size(); k++) {
            Map<String, Object> srcMap = src_list.get(k);
            // 写入200000条后切换到下个工作表
            if (rowNo % 20000 == 0) {
                System.out.println("Current Sheet:" + rowNo / 20000);
                sheet = wb.createSheet("Sheet" + (rowNo / 20000));// 建立新的sheet对象
                sheet = wb.getSheetAt(rowNo / 200000); // 动态指定当前的工作表
                XSSFRow head = sheet.createRow(0);
                for (int i = 0; i < handers.length; i++) {
                    XSSFCell cell = head.createCell(i);
                    cell.setCellValue(handers[i]);
                }
                for (int i = 0; i < downRows.size(); i++) {
                    Integer firstcol = downRows.get(i);
                    String[] textList = downData.get(i);
                    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
                    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createExplicitListConstraint(textList);
                    CellRangeAddressList addressList = null;
                    XSSFDataValidation validation = null;

                    addressList = new CellRangeAddressList(1, 20000, firstcol, firstcol);
                    validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);

                    validation.setSuppressDropDownArrow(true);
                    validation.setShowErrorBox(true);
                    sheet.addValidationData(validation);
                }
                pageRowNo = 0; // 新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for (int i = 0; i < titleKeyList.size(); i++) {
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
                // ---------------------------
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++); // 新建行对象

            // 行，获取cell值
            for (int j = 0; j < titleKeyList.size(); j++) {
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition",
                "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

}
